package cn.ac.cintcm.prespring.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.naming.NamingException;

import oracle.jdbc.OraclePreparedStatement;


public class OracleDb {
    private static HashMap<String, String> dbConfig;
    private static final String CONFIGSPILITOR = "=";
    
    private static void loadConfig() throws IOException, NamingException {
        String path = Config.DATA_DIR;
        File inputFile = new File(path, "config.txt");
        
        BufferedReader reader = new BufferedReader(new FileReader(inputFile));
        dbConfig = new HashMap<String, String>();
        String line = reader.readLine();
        while (line != null) {
            String[] name = getEntry(line);
            if (name != null && name.length == 2) {
                dbConfig.put(name[0].trim(), name[1].trim());
            }
            line = reader.readLine();
        }
        reader.close();
    }
    
    private static String[] getEntry(String line) {
        String[] items = null;
        if (line != null) {
            items =line.split(CONFIGSPILITOR);
        }
        return items;
    }

    public static Connection getConnection() {
        try {
            loadConfig();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        String url = (String) dbConfig.get("url");
        String userName = (String) dbConfig.get("user");
        String password = (String) dbConfig.get("password");
        Connection conn = null;
        try {
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
            conn = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static List<String> getColumn(String sql) {
        Connection conn = getConnection();
        Statement stmt = null;
        List<String> strings = new ArrayList<String>();
        try {
            stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            while(rst.next()) {
                strings.add(rst.getString(1));
            }
            rst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return strings;
    }
    
    public static List<String[]> getColumn(String sql, int size) {
        Connection conn = getConnection();
        Statement stmt = null;
        List<String[]> strings = new ArrayList<String[]>();
        try {
            stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            String[] strArry = null;
            int i = 0;
            while(rst.next()) {
                strArry = new String[size];
                i = 0;
                while (i < size) {
                    strArry[i] = rst.getString(++i);
                }
                strings.add(strArry);
            }
            rst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return strings;
    }

    public static boolean addColumn(String tableName, String fieldName, int size) {
        boolean isSuccess = false;
        String sql = "alter table " + tableName + " add " + fieldName + " varchar2(" + size+")";
        Connection conn = getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            isSuccess = stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return isSuccess;
    }
    
    public static boolean addNumberColumn(String tableName, String fieldName) {
        boolean isSuccess = false;
        String sql = "alter table " + tableName + " add " + fieldName + " number";
        Connection conn = getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            isSuccess = stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return isSuccess;
    }
    
    public static void update(List<String[]> result, String tableName, String fieldName) {
        if (result != null) {
            Connection conn = getConnection();
            PreparedStatement ps = null;
            try {
                conn.setAutoCommit(false);
                ps = conn.prepareStatement("update " + tableName +
                " set " + fieldName +"=? where id = ?" );
                ((OraclePreparedStatement) ps).setExecuteBatch(500);
                for (String[] s : result) {
                    ps.setString(1, s[1]);
                    ps.setString(2, s[0]);
                    System.out.println(s[0] +":"+ s[1]);
                    ps.executeUpdate();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    public static void updateNumber(List<String[]> result, String tableName, String fieldName) {
        if (result != null) {
            Connection conn = getConnection();
            PreparedStatement ps = null;
            try {
                conn.setAutoCommit(false);
                ps = conn.prepareStatement("update " + tableName +
                " set " + fieldName +"=? where id = ?" );
                ((OraclePreparedStatement) ps).setExecuteBatch(500);
                for (String[] s : result) {
                    ps.setInt(1, Integer.valueOf(s[1]));
                    ps.setString(2, s[0]);
                    System.out.println(s[0] +":"+ s[1]);
                    ps.executeUpdate();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
